package org.iabako.server.dao.business;

import org.iabako.server.serviceimpl.security.SessionUser;
import org.iabako.server.tools.BundleMessages;
import org.iabako.server.tools.ServerTools;
import org.iabako.shared.dto.FinancesBalance;
import org.iabako.shared.dto.FinancesChartData;
import org.iabako.shared.entity.user.Enterprise;
import org.iabako.shared.entity.user.User;
import org.iabako.shared.tools.GenericTools;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by jose on 12/11/14.
 */

@Repository("financeDAO")
public class FinanceDAO {

    @PersistenceContext(unitName = "MyPersistenceUnit")
    protected EntityManager entityManager;

    @Autowired
    BundleMessages messages;
    @Autowired
    SessionUser sessionUser;

    protected User getSessionUser() {
        return sessionUser != null ? sessionUser.getUser() : null;
    }

    public FinancesBalance getFinancesBalanceByDay(Date dateWithoutTime) {
        Date datePlusOne = new ServerTools().addDaysToDate(dateWithoutTime, 1);
        return getFinancesBalance(dateWithoutTime, datePlusOne, false);
    }

    public FinancesBalance getFinancesBalanceByMonth(Date dateWithoutTime) {
        Date datePlusOne = new ServerTools().addMonthsToDate(dateWithoutTime, 1);
        return getFinancesBalance(dateWithoutTime, datePlusOne, false);
    }

    public FinancesBalance getFinancesBalanceByYear(Date dateWithoutTime) {
        Date datePlusOne = new ServerTools().addYearsToDate(dateWithoutTime, 1);
        return getFinancesBalance(dateWithoutTime, datePlusOne, false);
    }

    public FinancesBalance getFinancesBalance(Date dateWithoutTime, Date datePlusOne, boolean onlyPayed) {

        String query = "SELECT SUM(p.amount) FROM Payment p join p.sale sale " +
                "WHERE p.date >= :date AND p.date < :datePlusOne AND sale.enterprise IN (:enterprises)";
        Double salesPayed = (Double) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                setParameter("date", dateWithoutTime).
                setParameter("datePlusOne", datePlusOne).
                getSingleResult();

        query = "SELECT SUM(e.paymentValue) FROM Expense e " +
                "WHERE e.paymentMethod IS NOT NULL " +
                "AND e.paymentDate >= :date AND e.paymentDate < :datePlusOne AND " +
                "e.enterprise IN (:enterprises)";
        Double expensesPayed = (Double) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                setParameter("date", dateWithoutTime).
                setParameter("datePlusOne", datePlusOne).
                getSingleResult();

        Double salesReceivable = 0D;
        Double expensesReceivable = 0D;
        if (!onlyPayed) {
            query = "SELECT SUM(i.amount) FROM Installment i join i.sale sale " +
                    "WHERE i.date >= :date AND i.date < :datePlusOne AND sale.enterprise IN (:enterprises)";
            salesReceivable = (Double) entityManager.createQuery(query).
                    setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                    setParameter("date", dateWithoutTime).
                    setParameter("datePlusOne", datePlusOne).
                    getSingleResult();

            query = "SELECT SUM(e.paymentValue) FROM Expense e " +
                    "WHERE e.paymentMethod IS NULL AND " +
                    "e.paymentDate >= :date AND e.paymentDate < :datePlusOne AND " +
                    "e.enterprise IN (:enterprises)";
            expensesReceivable = (Double) entityManager.createQuery(query).
                    setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                    setParameter("date", dateWithoutTime).
                    setParameter("datePlusOne", datePlusOne).
                    getSingleResult();
        }

        return new FinancesBalance(salesPayed, salesReceivable, expensesPayed, expensesReceivable);
    }

    public Long getNumberOfClientsWithPastInstallments() {
        return getNumberOfClientsWithPastInstallments(getSessionUser().getEnterprise());
    }

    public Long getNumberOfClientsWithPastInstallments(Enterprise enterprise) {

        String query = "SELECT COUNT(c.id) FROM Client c " +
                "WHERE c.status = '_1_inDebt' AND c.enterprise IN (:enterprises)";
        Long clientWithPastInstallments = (Long) entityManager.createQuery(query).
                setParameter("enterprises", enterprise.getVisibleEnterprises()).
                getSingleResult();

        return clientWithPastInstallments != null ? clientWithPastInstallments : 0L;
    }

    public Double getPastInstallmentsSum() {
        return getPastInstallmentsSum(getSessionUser().getEnterprise());
    }

    public Double getPastInstallmentsSum(Enterprise enterprise) {
        Date datePlusOne = new ServerTools().addDaysToDate(GenericTools.getDateWithoutTime(), 1);

        String query = "SELECT SUM(i.amount) FROM Installment i join i.sale sale " +
                "WHERE i.date < :tomorrow AND sale.enterprise IN (:enterprises)";
        Double pastInstallments = (Double) entityManager.createQuery(query).
                setParameter("enterprises", enterprise.getVisibleEnterprisesFinancialData()).
                setParameter("tomorrow", datePlusOne).
                getSingleResult();

        return pastInstallments != null ? pastInstallments : 0D;
    }

    public Double getPendingExpensesSum() {
        return getPendingExpensesSum(getSessionUser().getEnterprise());
    }

    public Double getPendingExpensesSum(Enterprise enterprise) {
        Date datePlusOne = new ServerTools().addDaysToDate(GenericTools.getDateWithoutTime(), 1);

        String query = "SELECT SUM(e.paymentValue) FROM Expense e " +
                "WHERE e.paymentDate < :tomorrow AND e.paymentMethod IS NULL AND e.enterprise IN (:enterprises)";
        Double pendingExpenses = (Double) entityManager.createQuery(query).
                setParameter("enterprises", enterprise.getVisibleEnterprisesFinancialData()).
                setParameter("tomorrow", datePlusOne).
                getSingleResult();

        return pendingExpenses != null ? pendingExpenses : 0D;
    }

    public Date getFirstDate() {
        String query = "SELECT MIN(e.paymentDate) FROM Expense e " +
                "WHERE e.paymentMethod IS NOT NULL AND e.enterprise IN (:enterprises)";
        Date firstExpenseDate = (Date) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                getSingleResult();

        firstExpenseDate = firstExpenseDate != null ? new ServerTools().resetTime(firstExpenseDate) : null;

        query = "SELECT MIN(p.date) FROM Payment p join p.sale sale WHERE sale.enterprise IN (:enterprises)";
        Date firstSaleDate = (Date) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData())
                .getSingleResult();

        firstSaleDate = firstSaleDate != null ? new ServerTools().resetTime(firstSaleDate) : null;

        if (firstExpenseDate == null && firstSaleDate == null) {
            //first date is today
            return new ServerTools().resetTime(new Date());
        } else if (firstSaleDate == null) {
            return firstExpenseDate;
        } else if (firstExpenseDate == null) {
            return firstSaleDate;
        }

        return firstExpenseDate.compareTo(firstSaleDate) > 0 ? firstSaleDate : firstExpenseDate;
    }

    public List<FinancesChartData> getFinancesChartDataForPayedExpenses(Date initialDate, Date finalDate) {
        List<FinancesChartData> datas = new ArrayList<FinancesChartData>();

        String query = "SELECT e.category, sum(e.paymentValue) as sum_by_category FROM Expense e " +
                "WHERE e.paymentMethod IS NOT NULL AND " +
                "e.paymentDate >= :initialDate AND e.paymentDate < :finalDate " +
                "AND e.enterprise IN (:enterprises) GROUP BY e.category order by sum_by_category desc";
        List<Object[]> result = (List<Object[]>) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                setParameter("initialDate", initialDate).
                setParameter("finalDate", finalDate).
                getResultList();

        double sum = 0;
        int count = 0;

        for (Object[] o : result) {
            if (count++ < 5) {
                FinancesChartData financesChartData = new FinancesChartData();
                financesChartData.setTitle((String) o[0]);
                financesChartData.addValue((Double) o[1]);
                datas.add(financesChartData);
            } else {
                sum += (Double) o[1];
            }
        }

        FinancesChartData financesChartData = new FinancesChartData();
        financesChartData.setTitle(messages.getLabel("home_expense_chart_other_categories"));
        financesChartData.addValue(sum);
        datas.add(financesChartData);

        return datas;
    }

    public List<FinancesChartData> getBestClients() {
        List<FinancesChartData> datas = new ArrayList<FinancesChartData>();

        String query = "SELECT c.firstName, c.lastName, c.name, c.number, SUM(p.amount) as sum_payment " +
                "FROM Payment p JOIN p.sale s JOIN s.client c " +
                "WHERE s.enterprise IN (:enterprises) GROUP BY c.id ORDER BY sum_payment desc";
        List<Object[]> result = (List<Object[]>) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                setMaxResults(5).getResultList();

        for (Object[] o : result) {
            FinancesChartData financesChartData = new FinancesChartData();
            financesChartData.setTitle(GenericTools.isEmpty(o[0], o[1]) ? ((String) o[2]) : ((String) o[0]).charAt(0) + " " + o[1]);
            financesChartData.addValue((Double) o[4]);
            financesChartData.addTooltip(o[2] + " (" + o[3] + ")\n" + messages.getLabel("home_client_income_label") + " : ");
            datas.add(financesChartData);
        }

        return datas;
    }

    public List<FinancesChartData> getWorstClients() {
        List<FinancesChartData> datas = new ArrayList<FinancesChartData>();

        String query = "SELECT c.firstName, c.lastName, c.name, c.number, SUM(i.amount) as sum_past_installment " +
                "FROM Installment i JOIN i.sale s JOIN s.client c " +
                "WHERE s.enterprise IN (:enterprises) AND i.date < :now " +
                "GROUP BY c.id ORDER BY sum_past_installment desc";

        ServerTools serverTools = new ServerTools();
        Date tomorrow = serverTools.addDaysToDate(new Date(), 1);

        List<Object[]> result = (List<Object[]>) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                setParameter("now", serverTools.resetTime(tomorrow)).
                setMaxResults(5).getResultList();

        for (Object[] o : result) {
            FinancesChartData financesChartData = new FinancesChartData();
            financesChartData.setTitle(GenericTools.isEmpty(o[0], o[1]) ? ((String) o[2]) : ((String) o[0]).charAt(0) + " " + o[1]);
            financesChartData.addValue((Double) o[4]);
            financesChartData.addTooltip(o[2] + " (" + o[3] + ")\n" + messages.getLabel("home_client_receivable_label") + " : ");
            datas.add(financesChartData);
        }

        return datas;
    }

    public List<FinancesChartData> getBestServices() {
        List<FinancesChartData> datas = new ArrayList<FinancesChartData>();

        String query = "SELECT service.number, service.name, SUM(ps.price) as sum_sold " +
                "FROM Sale s JOIN s.productsServices ps JOIN ps.service service WHERE s.enterprise IN (:enterprises) " +
                "GROUP BY service.id ORDER BY sum_sold desc";
        List<Object[]> result = (List<Object[]>) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                setMaxResults(5).getResultList();

        for (Object[] o : result) {
            FinancesChartData financesChartData = new FinancesChartData();
            financesChartData.setTitle((String) o[1]);
            financesChartData.addValue((Double) o[2]);
            financesChartData.addTooltip(o[1] + "\n" + messages.getLabel("home_service_income_label") + " : ");
            datas.add(financesChartData);
        }

        return datas;
    }

    public List<FinancesChartData> getBestProducts() {
        List<FinancesChartData> datas = new ArrayList<FinancesChartData>();

        String query = "SELECT product.number, product.name, SUM(ps.price) as sum_sold " +
                "FROM Sale s JOIN s.productsServices ps JOIN ps.product product WHERE s.enterprise IN (:enterprises) " +
                "GROUP BY product.id ORDER BY sum_sold desc";
        List<Object[]> result = (List<Object[]>) entityManager.createQuery(query).
                setParameter("enterprises", getSessionUser().getEnterprise().getVisibleEnterprisesFinancialData()).
                setMaxResults(5).getResultList();

        for (Object[] o : result) {
            FinancesChartData financesChartData = new FinancesChartData();
            financesChartData.setTitle((String) o[1]);
            financesChartData.addValue((Double) o[2]);
            financesChartData.addTooltip(o[1] + "\n" + messages.getLabel("home_product_income_label") + " : ");
            datas.add(financesChartData);
        }

        return datas;
    }
}
